﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data.SqlClient;

namespace DAL
{
    public class HocSinh_DAL
    {
        private DataProvider _provider = new DataProvider();
	
		    public HocSinh_DAL()
		    {
			    _provider.connect();
		    }
            public void ThemHocSinh(HocSinh_DTO hs)
            {
                string insertCommand = @"INSERT INTO HOCSINH (TenHS, Ngaysinh, Gioitinh, MaPH) VALUES('" +
                       hs.TenHS + "', '" +
                       hs.Ngaysinh + "', '" + hs.Gioitinh + "'," + hs.MaPH + ")";

                _provider.executeNonQuery(insertCommand);
            }

            public List<HocSinh_DTO> DS_HocSinh()
            {

                SqlDataReader reader = (SqlDataReader)_provider.executeQuery("DS_HocSinh");

                List<HocSinh_DTO> List_HocSinh = new List<HocSinh_DTO>();

                while (reader.Read())
                {
                    HocSinh_DTO hs = new HocSinh_DTO();
                    hs.MaHS = int.Parse(reader["MaHS"].ToString());
                    hs.TenHS = reader["TenHS"].ToString();
                    hs.Ngaysinh = Convert.ToDateTime(reader["Ngaysinh"]);
                    hs.Gioitinh = reader["Gioitinh"].ToString();
                    hs.MaPH = int.Parse(reader["MaPH"].ToString());
  
                    List_HocSinh.Add(hs);
                }

                reader.Close();
                return List_HocSinh;
            }

            public List<HocSinh_DTO> DS_HocSinhTheoMa(string MaHS)
            {
                // Một list các parameter
                List<SqlParameter> paramters = new List<SqlParameter>();
                paramters.Add(new SqlParameter("@MaHS", MaHS));

                //string query = "SELECT * FROM HOCSINH WHERE MAHS LIKE '%"+ MaHS +"%'";

                // Gọi hàm excute sử dujg procedure có parameter
                SqlDataReader reader = (SqlDataReader)_provider.executeQueryParameter("DS_HocSinhTheoMa", paramters);

                List<HocSinh_DTO> List_HS = new List<HocSinh_DTO>();

                while (reader.Read())
                {
                    HocSinh_DTO HS = new HocSinh_DTO();
                    HS.MaHS = int.Parse(reader["MaHS"].ToString());
                    HS.TenHS = reader["TenHS"].ToString();
                    HS.Ngaysinh = Convert.ToDateTime(reader["Ngaysinh"]);
                    HS.Gioitinh = reader["Gioitinh"].ToString();
                    HS.MaPH = int.Parse(reader["MaPH"].ToString());
                   
                    List_HS.Add(HS);
                }

                reader.Close();
                return List_HS;
            }

            public List<HocSinh_DTO> DS_HocSinhTheoTen(string TenHS)
            {
                // Một list các parameter
                List<SqlParameter> paramters = new List<SqlParameter>();
                paramters.Add(new SqlParameter("@TenHS", TenHS));

               

                // Gọi hàm excute sử dujg procedure có parameter
                SqlDataReader reader = (SqlDataReader)_provider.executeQueryParameter("DS_HocSinhTheoTen", paramters);

                List<HocSinh_DTO> List_HS = new List<HocSinh_DTO>();

                while (reader.Read())
                {
                    HocSinh_DTO HS = new HocSinh_DTO();
                    HS.MaHS = int.Parse(reader["MaHS"].ToString());
                    HS.TenHS = reader["TenHS"].ToString();
                    HS.Ngaysinh = Convert.ToDateTime(reader["Ngaysinh"]);
                    HS.Gioitinh = reader["Gioitinh"].ToString();
                    HS.MaPH = int.Parse(reader["MaPH"].ToString());

                    List_HS.Add(HS);
                }

                reader.Close();
                return List_HS;
            }
            public List<HocSinh_DTO> DS_HocSinhTheoLop(string Malop)
            {
                // Một list các parameter
                List<SqlParameter> paramters = new List<SqlParameter>();
                paramters.Add(new SqlParameter("@Malop", Malop));



                // Gọi hàm excute sử dujg procedure có parameter
                SqlDataReader reader = (SqlDataReader)_provider.executeQueryParameter("DS_HocSinhTheoLop", paramters);

                List<HocSinh_DTO> List_HS = new List<HocSinh_DTO>();

                while (reader.Read())
                {
                    HocSinh_DTO HS = new HocSinh_DTO();
                    HS.MaHS = int.Parse(reader["MaHS"].ToString());
                    HS.TenHS = reader["TenHS"].ToString();
                    HS.Ngaysinh = Convert.ToDateTime(reader["Ngaysinh"]);
                    HS.Gioitinh = reader["Gioitinh"].ToString();
                    HS.MaPH = int.Parse(reader["MaPH"].ToString());

                    List_HS.Add(HS);
                }

                reader.Close();
                return List_HS;
            }

            public void Xoa(HocSinh_DTO hs)
            {
                string deleteCommand1 = "DELETE FROM HOCSINH WHERE MaHS = " + hs.MaHS;
                string deleteCommand2 = "DELETE FROM DANHSACHLOP WHERE MaHS= " + hs.MaHS;
                _provider.executeNonQuery(deleteCommand2);
                _provider.executeNonQuery(deleteCommand1);

            }
   
         
            

    }
}
